package com.xzy.jooq.learn.test;

import com.xzy.jooq.learn.codegen.tables.records.LjUserRecord;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import static com.xzy.jooq.learn.codegen.Tables.LJ_USER;


/**
 * 基础CRUD
 * <p>
 * 通过 DSLContext API 和 Record API，可以完成基础CURD操作
 *
 * @author xzy.xiao
 * @date 2022/6/27  19:08
 */
public class Main {

    private static final String JDBC_URL = "jdbc:mysql://127.0.0.1:3306/learn_jooq?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false";
    private static final String JDBC_USERNAME = "root";
    private static final String JDBC_PASSWORD = "123456";

    public static void main(String[] args) {

        // 获取数据库连接
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD)) {

            // 获取 JOOQ 执行器
            DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);

//            insertByDslContextApi(dslContext);
//            insertByRecordApi(dslContext);
//            batchInsertByDslContextApi(dslContext);
//            insertAndGetAutoGeneratedValue(dslContext);
//            insertOrUpdate(dslContext);
//            updateByDslContextApi(dslContext);
//            updateByRecordApi(dslContext);
//            batchUpdate(dslContext);
//            delete(dslContext);


        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    /**
     * 删除
     */
    private static void delete(DSLContext dslContext) {
        // 方法一：DslContext API
        int result1 = dslContext.delete(LJ_USER)
                .where(
                        LJ_USER.REAL_NAME.eq("张三")
                                .and(LJ_USER.USERNAME.eq("zhangsan"))
                ).execute();

        // 方法二：Record API
        LjUserRecord record = dslContext.newRecord(LJ_USER);
        record.setRealName("张三");
        record.setUsername("zhangsan");
        int result2 = record.delete();
    }

    /**
     * 批量更新
     */
    private static void batchUpdate(DSLContext dslContext) {
        LjUserRecord record1 = new LjUserRecord();
        record1.setId(1);
        record1.setUsername("AAA");

        LjUserRecord record2 = new LjUserRecord();
        record2.setId(2);
        record2.setUsername("BBB");

        List<LjUserRecord> records = new ArrayList<>();
        records.add(record1);
        records.add(record2);

        int[] results = dslContext.batchInsert(records).execute();
    }

    /**
     * 通过 Record 相关 API 实现数据编辑
     */
    private static void updateByRecordApi(DSLContext dslContext) {
        Integer id = dslContext.insertInto(LJ_USER, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values("张三", "zhangsan", "123abd,", (byte) 0)
                .returning(LJ_USER.ID)
                .fetchOne()
                .getId();
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(id)).fetch());


        LjUserRecord userRecord = dslContext.newRecord(LJ_USER);
        userRecord.setId(id);
        userRecord.setRealName("张三_b");
        userRecord.setUsername("zhangsan_b");
        int result = userRecord.update();// 默认使用主键作为更新条件
        System.out.printf("编辑的数据条数：%d\n", result);
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(id)).fetch());
    }

    /**
     * 通过 DSLContext 相关 API 实现数据编辑
     */
    private static void updateByDslContextApi(DSLContext dslContext) {
        Integer id = dslContext.insertInto(LJ_USER, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values("张三", "zhangsan", "123abd,", (byte) 0)
                .returning(LJ_USER.ID)
                .fetchOne()
                .getId();
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(id)).fetch());

        int result1 = dslContext.update(LJ_USER)
                .set(LJ_USER.REAL_NAME, "张三_b")
                .set(LJ_USER.USERNAME, "zhangsan_b")
                .where(LJ_USER.ID.eq(id))
                .execute();
        System.out.printf("编辑的数据条数：%d\n", result1);
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(id)).fetch());
    }

    /**
     * 新增或编辑
     */
    private static void insertOrUpdate(DSLContext dslContext) {
        Integer recordId = dslContext.insertInto(LJ_USER, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values("张三", "zhangsan", "123abd,", (byte) 0)
                .returning(LJ_USER.ID)
                .fetchOne()
                .getId();
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(recordId)).fetch());


        // 主键重复：终止插入操作
        int result1 = dslContext.insertInto(LJ_USER, LJ_USER.ID, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values(recordId, "张三", "zhangsan", "123abd,", (byte) 0)
                .onDuplicateKeyIgnore()
                .execute();
        System.out.printf("编辑的数据条数：%d\n", result1);
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(recordId)).fetch());

        // 主键重复：变为更新操作
        int result2 = dslContext.insertInto(LJ_USER, LJ_USER.ID, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values(recordId, "张三", "zhangsan", "123abd,", (byte) 0)
                .onDuplicateKeyUpdate() // 主键重复时执行下列更新操作
                .set(LJ_USER.REAL_NAME, "张三_b")
                .set(LJ_USER.USERNAME, "zhangsan_b")
                .execute();
        System.out.printf("编辑的数据条数：%d\n", result2);
        System.out.printf("数据：%s\n", dslContext.select().from(LJ_USER).where(LJ_USER.ID.eq(recordId)).fetch());
    }

    /**
     * 获取自动生成的数据
     */
    private static void insertAndGetAutoGeneratedValue(DSLContext dslContext) {
        // 方法一：通过 returning() 返回需要的数据
        LjUserRecord userRecord1 = dslContext.insertInto(LJ_USER, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values("张三", "zhangsan", "123abd,", (byte) 0)
                .returning(LJ_USER.ID)
                .fetchOne();
        Integer insertedUserId1 = userRecord1.getId();
        System.out.printf("新增User的ID：%d\n", insertedUserId1);

        // 方法二：数据会自动回填到 Record
        LjUserRecord userRecord2 = dslContext.newRecord(LJ_USER);
        userRecord2.setRealName("张三A");
        userRecord2.setUsername("zhangsan_a");
        userRecord2.setPassword("123abd");
        userRecord2.setIsDeleted((byte) 0);
        userRecord2.insert();
        Integer insertedUserId2 = userRecord2.getId();
        System.out.printf("新增User的ID：%d\n", insertedUserId2);
    }

    /**
     * 批量插入
     */
    private static void batchInsertByDslContextApi(DSLContext dslContext) {
        List<LjUserRecord> userRecordList = IntStream.range(0, 10).mapToObj(i -> {
            LjUserRecord userRecord = new LjUserRecord();
            userRecord.setRealName("张三" + i);
            userRecord.setUsername("zhangsan_" + i);
            userRecord.setPassword("123abd");
            userRecord.setIsDeleted((byte) 0);
            return userRecord;
        }).collect(Collectors.toList());
        int[] results = dslContext.batchInsert(userRecordList).execute();
        System.out.printf("批量插入结果：%s\n", Arrays.toString(results));
    }

    /**
     * 通过 Record 相关 API 实现数据插入
     */
    private static void insertByRecordApi(DSLContext dslContext) {
        LjUserRecord userRecord = dslContext.newRecord(LJ_USER);
        userRecord.setRealName("张三A");
        userRecord.setUsername("zhangsan_a");
        userRecord.setPassword("123abd");
        userRecord.setIsDeleted((byte) 0);
        int result = userRecord.insert();
        System.out.printf("成功插入%d条数据\n", result);
    }

    /**
     * 通过 DSLContext 相关 API 实现数据插入
     */
    private static void insertByDslContextApi(DSLContext dslContext) {

        // 方式一
        int result1 = dslContext.insertInto(LJ_USER, LJ_USER.REAL_NAME, LJ_USER.USERNAME, LJ_USER.PASSWORD, LJ_USER.IS_DELETED)
                .values("张三A", "zhangsan_a", "123abd,", (byte) 0)
                .values("张三B", "zhangsan_b", "123abd,", (byte) 0)
                .execute();
        System.out.printf("成功插入%d条数据\n", result1);

        // 方式二
        int result2 = dslContext.insertInto(LJ_USER)
                .set(LJ_USER.REAL_NAME, "张三C")
                .set(LJ_USER.USERNAME, "zhangsan_c")
                .set(LJ_USER.PASSWORD, "123abc,")
                .set(LJ_USER.IS_DELETED, (byte) 0)
                .newRecord()
                .set(LJ_USER.REAL_NAME, "张三D")
                .set(LJ_USER.USERNAME, "zhangsan_d")
                .set(LJ_USER.PASSWORD, "123abc,")
                .set(LJ_USER.IS_DELETED, (byte) 0)
                .execute();
        System.out.printf("成功插入%d条数据\n", result2);

    }


}
